<?

function update_payed_sales_rp($reseller_ids,$payment_date,$before_date,$type='rp'){
	foreach($reseller_ids as $k=>$reseller_id){
		if($type=='rp'){
			$queries[$k][] = "UPDATE sales SET payed='true',payment_date='$payment_date' WHERE id=$reseller_id AND payed='false' AND date<'$before_date' AND state='normal'";
			$queries[$k][] = "UPDATE reseller_links r left join sales s on r.child_id=s.id SET s.ref_payed='true',ref_payment_date='$payment_date' WHERE r.parent_id=$reseller_id and r.link_type='affiliate' AND s.ref_payed='false' AND s.date<'$before_date' AND s.state='normal'";
		}elseif($type=='rp2'){
 			$queries[$k][] = "UPDATE sales SET payed_reseller='$payment_date' WHERE payed_reseller isnull AND order_id IN(SELECT order_id FROM orders WHERE reseller_id=$reseller_id AND date<'$before_date')";
                        $queries[$k][] = "UPDATE sales SET payed_referer='$payment_date' WHERE payed_referer isnull AND order_id in(SELECT order_id FROM orders WHERE reseller_id in(SELECT reseller_id FROM resellers WHERE referer_id=$reseller_id) AND date<'$before_date')";
		}
	}
	return $queries;
}
//-----------------------------------------------------------------------------------------------------------

function get_cpanel_affiliate_sales($id,$period='',$issued=''){
	$local_connect = $GLOBALS['master_db'];
	$where = '';
	if($period){
		$where .= "  AND date~*'$period' ";
	}
	if($issued){
		$where .= " AND paid_reseller~*'$issued'";
	}else{
		$where .= " AND paid_reseller isnull ";
	}

	$query = "SELECT invoice_id, substring(date from 1 for 10) as date, affiliate, type, 'order' as sale_type, amount, total_price , plan_name, round((amount/10),2) as amount_cpanel_affiliate, 'USD' as payment_currency, paid_reseller FROM affiliates.payments WHERE reseller_id=$id $where ORDER BY id DESC";
	
	$local_connect->query($query);
	if($local_connect->error){
		//echo $local_connect->error;
		return false;
	}
	return $local_connect->getAll();	
}
//-----------------------------------------------------------------------------------------------------------

function get_cpanel_affiliate_sales_($id,$sdate='',$edate='',$group_by=false){
	$local_connect = $GLOBALS['master_db'];
	$where = '';
	if($sdate){
		$where .= " AND  date>='$sdate'";
	}
	if($edate){
		$where .= " AND  date<='$edate'";
	}
	$query = "
		SELECT 
			invoice_id as order_id,
			substring(date from 1 for 10) as order_date,
			affiliate as reseller,
			'order' as sale_type,
			plan_name as product_type,
			amount as amount_wholesale,
			total_price as amount_total,
			round((amount/10),2) as amount_referer,
			'USD' as payment_currency_,
			paid_reseller as issued_date
		FROM 
			affiliates.payments 
		WHERE 
			reseller_id=$id $where 
		ORDER BY 
			id DESC
		";

	if($group_by){
		$query = "SELECT reseller_id, sum(round((amount/10),2)) as amount_referer,sum(amount) as amount_wholesale,sum(total_price) as amount_total, 'USD' as payment_currency_ FROM affiliates.payments WHERE reseller_id=$id $where GROUP BY reseller_id";
		//echo $query;
	}
	
	$local_connect->query($query);
	if($local_connect->error){
		//echo $local_connect->error;
		return false;
	}
	return $local_connect->getAll();	
}
//-----------------------------------------------------------------------------------------------------------

function get_payment_history__($id,$for_issued=''){	
	$local_connect = $GLOBALS['master_db'];
	if($for_issued){
		$where = "reseller_id=$id and date>='2005-10-05' and date~*'$for_issued'";
	}else{
		$where = "reseller_id=$id and date>='2005-10-05'";
	}	
	$query = "SELECT payment_id,reseller_id,first_name,last_name,address1,address2,city,state,zip,country,countries.name as country_long,substring(date from 1 for 10) as issued,substring(date from 1 for 7) as period,payment_method,status,amount,check_number,paypal_email,paypal_transaction_id,memo,currency,paypal_transaction_id,substring(change_status_date from 1 for 10) as change_status_date,swift_code,bank_name,bank_address,account_number,routing_number,account_holder FROM payments left join countries ON(payments.country=countries.iso2) WHERE $where  ORDER BY payment_id desc";
	$local_connect->query($query);
	$res = $local_connect->getAll();
	foreach($res as $k=>$v){
		$query2 = "SELECT sum(amount_total-amount_wholesale) as amount_sales,count(distinct(order_id)) FROM sales WHERE reseller_id=$id AND payed_reseller~*'$v[issued]'";
		$query3 = "SELECT sum(amount_referer) as amount_affiliates,count(distinct(order_id)) FROM sales WHERE reseller_id IN(SELECT reseller_id FROM resellers WHERE referer_id=$id) AND amount_referer!=0 AND payed_referer~*'$v[issued]'";
		$query5 = "SELECT round(sum(amount/10),2) as amount_cpanel_affiliates, count(id) FROM affiliates.payments WHERE reseller_id=$id AND paid_reseller~*'$v[issued]'";
		$query4 = "SELECT substring(date from 1 for 10) as date, msg FROM payments_msg WHERE payment_id=$v[payment_id] AND show_flag='t' ORDER BY id DESC";

		$local_connect->query($query2);
		$tmp = $local_connect->getNext();
		$res[$k]['count_sales'] = ($tmp[count] ? $tmp[count] : 0);
		$res[$k]['amount_sales'] = ($tmp[amount_sales] ? sprintf('%0.2f',$tmp[amount_sales]) : sprintf('%0.2f',0));

		$local_connect->query($query3);
		$tmp = $local_connect->getNext();
		$res[$k]['count_affiliates'] = ($tmp[count] ? $tmp[count] : 0);
		$res[$k]['amount_affiliates'] = ($tmp[amount_affiliates] ? sprintf('%0.2f',$tmp[amount_affiliates]) : sprintf('%0.2f',0));

		$local_connect->query($query5);
		$tmp = $local_connect->getNext();
		if($tmp){
			$res[$k]['count_cpanel_affiliates'] = $tmp['count'];
			$res[$k]['amount_cpanel_affiliates'] = sprintf('%0.2f',$tmp['amount_cpanel_affiliates']);
			$res[$k]['count_affiliates'] = $res[$k]['count_affiliates'] + $res[$k]['count_cpanel_affiliates'];
			$res[$k]['amount_affiliates'] = $res[$k]['amount_affiliates'] + $res[$k]['amount_cpanel_affiliates'];
		}
		
		$local_connect->query($query4);
		$res[$k]['messages'] = $local_connect->getAll();
		
		$res[$k]['count'] = $res[$k]['count_sales'] + $res[$k]['count_affiliates'];
	}
	return $res;
}
//-----------------------------------------------------------------------------------------------------------

function get_reseller_commission_ ($id,$issued=''){ // '' -> not payed
        $local_connect = $GLOBALS['master_db'];
	$ret = array();
        $query0 = "SELECT first_name,last_name,address1,address2,city,state,zip,country,date,countries.name as country_long,release_level,payment_method,paypal_email,swift_code,bank_name,bank_address,account_number,routing_number,account_holder FROM resellers left join countries on(resellers.country=countries.iso2) WHERE reseller_id=$id";
	if($issued){
		$query1 = "SELECT count(distinct(o.order_id)) as count_sales,sum(s.amount_total-s.amount_wholesale) as amount_sales FROM sales s LEFT JOIN orders o USING(order_id) WHERE o.reseller_id=$id AND o.order_status IN(16,32,1024,1,2,4,8,64) AND o.order_number notnull AND s.payed_reseller ~* '$issued'";
		$query2 = "SELECT count(distinct(o.order_id)) as count_affiliates,sum(s.amount_referer) as amount_affiliates FROM sales s LEFT JOIN orders o USING(order_id) WHERE o.reseller_id IN(SELECT reseller_id FROM resellers WHERE referer_id=$id) AND s.amount_referer!=0 AND o.order_status IN(16,32,1024,1,2,4,8,64) AND o.order_number notnull AND s.payed_referer ~* '$issued'";
		$query3 = "SELECT count(id) as count_cpanel_affiliates,round(sum(amount/10),2) as amount_cpanel_affiliates FROM affiliates.payments WHERE reseller_id=$id AND paid_reseller ~* '$issued'";
	}else{
		$query1 = "SELECT count(distinct(o.order_id)) as count_sales,sum(s.amount_total-s.amount_wholesale) as amount_sales, substring(o.date from 1 for 7) as period FROM sales s LEFT JOIN orders o USING(order_id) WHERE o.reseller_id=$id AND o.order_status IN(16,32,1024,1,2,4,8,64) AND o.order_number notnull AND s.payed_reseller isnull GROUP BY substring(o.date from 1 for 7) HAVING sum(s.amount_total-s.amount_wholesale)>0 ORDER BY substring(o.date from 1 for 7) DESC";
		$query2 = "SELECT count(distinct(o.order_id)) as count_affiliates,sum(s.amount_referer) as amount_affiliates, substring(o.date from 1 for 7) as period FROM sales s LEFT JOIN orders o USING(order_id) WHERE o.reseller_id IN(SELECT reseller_id FROM resellers WHERE referer_id=$id) AND s.amount_referer!=0 AND o.order_status IN(16,32,1024,1,2,4,8,64) AND o.order_number notnull AND s.payed_referer isnull GROUP BY substring(o.date from 1 for 7) HAVING sum(s.amount_referer)>0 ORDER BY substring(o.date from 1 for 7) DESC";
		$query3 = "SELECT count(id) as count_cpanel_affiliates,round(sum(amount/10),2) as amount_cpanel_affiliates, substring(date from 1 for 7) as period FROM affiliates.payments WHERE reseller_id=$id AND paid_reseller isnull GROUP BY substring(date from 1 for 7) HAVING round(sum(amount/10),2)>0 ORDER BY substring(date from 1 for 7) DESC";
	}

	$local_connect->query($query0);
	if($local_connect->error){
		return $ret;
	}
	$tmp = $local_connect->getNext();
	
	$local_connect->query($query1);
	if($local_connect->error){
		return $ret;
	}
	$some_array[] = $local_connect->getAll();
	
	$local_connect->query($query2);
	if($local_connect->error){
		return $ret;
	}
	$some_array[] = $local_connect->getAll();

	$local_connect->query($query3);
	if($local_connect->error){
		return $ret;
	}
	$some_array[] = $local_connect->getAll();
	
	foreach($some_array as $k=>$v){
		foreach($v as $kk=>$vv){
			if($ret[$vv[period]]){
				$ret[$vv[period]] = array_merge($ret[$vv[period]],$vv);
			}else{
				$ret[$vv[period]] = array_merge($vv,$tmp);
			}
			
		}
	}
	
	foreach($ret as $period=>$content){
                if(!$content[count_sales]){
                        $ret[$period][count_sales] = 0;
		}
		if(!$content[count_affiliates]){
			$ret[$period][count_affiliates] = 0;	
		}
                if(!$content[count_cpanel_affiliates]){
                        $ret[$period][count_cpanel_affiliates] = 0;
		}
                if(!$content[amount_sales]){
                        $ret[$period][amount_sales] = sprintf('%0.2f',0);
                }
                if(!$content[amount_affiliates]){
                        $ret[$period][amount_affiliates] = sprintf('%0.2f',0);
                }
                if(!$content[amount_cpanel_affiliates]){
                        $ret[$period][amount_cpanel_affiliates] = sprintf('%0.2f',0);
                }
                
                $ret[$period][count_affiliates] = $ret[$period][count_affiliates] + $ret[$period][count_cpanel_affiliates];
                $ret[$period][amount_affiliates] = $ret[$period][amount_affiliates] + $ret[$period][amount_cpanel_affiliates];
		$ret[$period][amount] =  $ret[$period][amount_sales] + $ret[$period][amount_affiliates];
		$ret[$period][count] = $ret[$period][count_sales] + $ret[$period][count_affiliates];
	}

	uksort($ret,'sort_periods');	
	return $ret;
}
//-----------------------------------------------------------------------------------------------------------

function list_sales($wh='',$options='',$return_type='struct',$request_type=''){
	$local_connect = $GLOBALS['master_db'];
	$map = array(
			'sale_id'=>'s.sale_id',
			'product_id'=>'s.product_id',
			'amount_total'=>'s.amount_total',
			'amount_wholesale'=>'s.amount_wholesale',
			'amount_referer'=>'s.amount_referer',
                        'amount_vat'=>'s.amount_vat',
			'amount_fee'=>'s.amount_fee',
			'sale_date'=>'to_char(s.date, \'YYYY-MM-DD\')',
			'sale_number'=>'s.order_number',
			'period'=>'s.period',
			'sale_name'=>'s.name',
			'sale_type'=>'s.type',
			'sale_type_'=>'s.type',
			'payed_reseller'=>'to_char(s.payed_reseller, \'YYYY-MM-DD\')',
			'payed_referer'=>'to_char(s.payed_referer, \'YYYY-MM-DD\')',
			'product_type'=>'p.type',
			'product_name'=>'p.name ',
			'sale_period'=>'s.sale_period',
			'sale_state'=>'s.state',
			'purchase_id'=>'pr.purchase_id',
			'purchase_start_date'=>'pr.start_date',
			'purchase_expire_date'=>'pr.expire_date',
			'purchase_expire_date_'=>'pr.expire_date',
			'domain_sld'=>'rgdm.sld',
			'domain_tld'=>'rgdm.tld',
			'domain_flags'=>'rgdm.flags',
			'order_id'=>'o.order_id',
			'order_date'=>'to_char(o.date, \'YYYY-MM-DD\')',
			'order_number'=>'o.order_number',
			'order_status'=>'o.order_status',
			'company_id'=>'o.company_id',
			'store_id'=>'o.store_id',
			'store'=>'st.title',
			'reseller_id'=>'o.reseller_id',
			'reseller'=>'r.name',
			'reseller_country'=>'r.country',
			'reseller_blocked'=>'r.blocked',
			'client_id'=>'o.client_id',
			'username'=>'cl.username',
			'client_server'=>'cl.server',
			'client_blocked' =>'cl.blocked',
			'affiliate_id'=>'o.affiliate_id',
			'referer_id'=>'o.referer_id',
			'payment_method'=>'o.payment_method',
			'currency'=>'s.currency',
			'payment_currency'=>'o.payment_currency',
			'payment_currency_'=>'o.payment_currency',
			'payment_exchange'=>'o.payment_exchange',
			'client_company_name'=>'o.client_company_name',
			'client_vat_num'=>'o.client_vat_num',
			'client_firstname'=>'o.client_firstname',
			'client_lastname'=>'o.client_lastname',
			'client_email'=>'o.client_email',
			'client_address1'=>'o.client_address1',
			'client_address2'=>'o.client_address2',
			'client_city'=>'o.client_city',
			'client_state'=>'o.client_state',
			'client_zip'=>'o.client_zip',
			'client_country'=>'o.client_country',
			'client_country_'=>'o.client_country',
			'client_phone'=>'o.client_phone',
			'client_fax'=>'o.client_fax',
			'client_ip'=>'o.ip'		
	);

//	$table = "sales s LEFT JOIN products p ON(s.product_id=p.product_id) LEFT JOIN purchases pr ON(s.purchase_id=pr.purchase_id) LEFT JOIN orders o ON(s.order_id=o.order_id) LEFT JOIN stores st ON(o.store_id=st.store_id) LEFT JOIN resellers r ON (o.reseller_id=r.reseller_id) LEFT JOIN clients cl ON(o.client_id=cl.client_id)";
	$table = "sales s LEFT JOIN orders o ON(s.order_id=o.order_id) LEFT JOIN clients cl ON(o.client_id=cl.client_id) LEFT JOIN resellers r ON (o.reseller_id=r.reseller_id) LEFT JOIN stores st ON(o.store_id=st.store_id) LEFT JOIN purchases pr ON(s.purchase_id=pr.purchase_id) LEFT JOIN registerdomain rgdm ON(pr.purchase_id=rgdm.purchase_id) LEFT JOIN products p ON(s.product_id=p.product_id)";

	if($options[group_by] and is_array($options[group_by])){
		if(!in_array('payment_currency_',$options[group_by])){
			$map['count']="count(distinct(o.order_id))";
			$map[amount_total]="sum(s.amount_total)";
			$map[amount_wholesale]="sum(s.amount_wholesale)";
			$map[amount_reseller]="sum(s.amount_total-s.amount_wholesale)";
			$map[amount_referer]="sum(s.amount_referer)";
			$map[amount_vat]="sum(s.amount_vat)";
			$map[amount_fee]="sum(s.amount_fee)";
                }else{
			$map['count']="count(distinct(o.order_id))";
			$map[amount_total]="round(sum(s.amount_total*o.payment_exchange),2)";
			$map[amount_wholesale]="round(sum(s.amount_wholesale*o.payment_exchange),2)";
			$map[amount_reseller]="round(sum(s.amount_total-s.amount_wholesale),2)";
			$map[amount_referer]="round(sum(s.amount_referer),2)";
			$map[amount_vat]="round(sum(s.amount_vat*o.payment_exchange),2)";
			$map[amount_fee]="round(sum(s.amount_fee*o.payment_exchange),2)";
                }
		foreach($options[group_by] as $v){
			if(in_array($v, array_keys($map))){
				$group_by[]=$map[$v];
				$params[]=$map[$v]." as ".$v;
			}
		}
		if($wh and is_array($wh)){
			foreach($wh as $wh_k => $wh_v){
				if(is_numeric($wh_v)){
					$where[] = $map[$wh_k]."=".$wh_v;
				}elseif(is_array($wh_v)){
					$tmp=array();
					foreach($wh_v as $vv){
						if(!in_array($wh_k,array('sale_date','order_date','sale_type_','client_country_','purchase_expire_date_'))){
							if(is_numeric($vv))
								$tmp[]=$map[$wh_k]."=".$vv;
							elseif($vv=='null')
								$tmp[]=$map[$wh_k]." is".$vv;
							elseif($vv=='isnull' or $vv=='notnull')
								$tmp[]=$map[$wh_k].' '.$vv;
							else
								$tmp[]=$map[$wh_k]."~*'$vv'";
						}else{
							if(in_array($wh_k,array('sale_type_','client_country_'))){
								$tmp[]=$map[$wh_k].substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}else{
								$where[]=$map[$wh_k].substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}
						}
					}
					if($tmp){
						$tmp = implode(' OR ',$tmp);
						$where[]="($tmp)";
					}
				}else{
					$where[] = $map[$wh_k]."~*'".$wh_v."'";
				}
			}
		}

		if($options[order_by] and ($options[order_by_]=='DESC' or $options[order_by_]=='ASC')){
			$order_by = "ORDER BY ".$map[($options[order_by])]." ".$options[order_by_];
		}

		if($where)
			$where = implode(' AND ',$where);
			
		if(!in_array('payment_currency_',$options[group_by])){
			$params[]="sum(s.amount_total) as amount_total";
			$params[]="sum(s.amount_wholesale) as amount_wholesale";
			$params[]="sum(s.amount_referer) as amount_referer";
			$params[]="sum(s.amount_total-s.amount_wholesale) as amount_reseller";
			$params[]="sum(s.amount_vat) as amount_vat";
			$params[]="sum(s.amount_fee) as amount_fee";
			$params[]="count(distinct(o.order_id))";               
                }else{
			$params[]="round(sum(s.amount_total*o.payment_exchange),2) as amount_total";
			$params[]="round(sum(s.amount_wholesale*o.payment_exchange),2) as amount_wholesale";
			$params[]="round(sum(s.amount_referer),2) as amount_referer";
			$params[]="round(sum(s.amount_total-s.amount_wholesale),2) as amount_reseller";
			$params[]="round(sum(s.amount_vat*o.payment_exchange),2) as amount_vat";
			$params[]="round(sum(s.amount_fee*o.payment_exchange),2) as amount_fee";
			$params[]="count(distinct(o.order_id))";               
                }
                
		$params = implode(',',$params);
		$group_by = "GROUP BY ".implode(',',$group_by);
		if($options[limit])
			$limit = "LIMIT ".$options[limit];
		else
			$limit = "LIMIT 50";
		if($options[offset])
			$offset = "OFFSET ".$options[offset];
		else
			$offset = "OFFSET 0";
                                        
	}else{
		foreach($map as $k => $v){
			$params[]=$v." as ".$k;
			if($wh and is_array($wh) and $wh[$k]){
				if(is_numeric($wh[$k])){
					$where[] = $v."=".$wh[$k];
				}elseif(is_array($wh[$k])){
					$tmp=array();
					foreach($wh[$k] as $vv){
						if(!in_array($k,array('sale_date','order_date','sale_type_','client_country_','purchase_expire_date_'))){
							if(is_numeric($vv))
								$tmp[] = $v."=".$vv;
							elseif($vv=='null')
								$tmp[] = $v." is".$vv;
							elseif($vv=='isnull' or $vv=='notnull')
								$tmp[] = $v.' '.$vv;
							else
								$tmp[] = "$v~*'$vv'";
						}else{
							if(in_array($k,array('sale_type_','client_country_'))){
								$tmp[] = $v.substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}else{
								$where[]=$v.substr($vv,0,2)."'".substr($vv,2,strlen($vv))."'";
							}
						}
					}
					if($tmp){
						$tmp = implode(' OR ',$tmp);
						$where[]="($tmp)";
					}					
				}else{
					$where[] = $v."~*'".$wh[$k]."'";
				}
			}
		}
		if($options[order_by] and ($options[order_by_]=='DESC' or $options[order_by_]=='ASC')){
			$order_by = "ORDER BY ".$map[($options[order_by])]." ".$options[order_by_];
		}
		if($where)
			$where = implode(' AND ',$where);
		$params = implode(',',$params);
		if($options[limit])
			$limit = "LIMIT ".$options[limit];
		else
			$limit = "LIMIT 50";
		if($options[offset])
			$offset = "OFFSET ".$options[offset];
		else
			$offset = "OFFSET 0";
		if(!$order_by)
			$order_by = "ORDER BY o.order_id DESC";
	}

	//-------------------------------------------------
        $where = preg_replace("/o\.payment_method~\*/","o.payment_method=",$where);
        $where = preg_replace("/p\.type~\*/","p.type=",$where);
	//-------------------------------------------------

	if($request_type=='affiliates'){
		$order_period = "o.order_number notnull AND (s.payed_referer>'2005-10-04' or s.payed_referer isnull) AND s.amount_referer!=0 AND";
	}else{
		$order_period = "o.order_number notnull AND (s.payed_reseller>'2005-10-04' or s.payed_reseller isnull) AND";
	}
	$query1 = 'SELECT '.$params.' FROM '.$table.' WHERE '.$order_period.' '.$where.' '.$group_by.' '.$order_by.' '.$limit.' '.$offset;
	$query2 = 'SELECT '.$params.' FROM '.$table.' WHERE '.$order_period.' '.$where.' '.$group_by;
	
	if($return_type=='query'){
		return $query1;
	}
	
	$local_connect->query($query1);
	if($local_connect->error){
		return false;
	}
	$res['result'] = $local_connect->getAll();
	$local_connect->query($query2);
	if($local_connect->error){
		return false;
	}
	$res['num_rows'] = $local_connect->numRows();
	return($res);
}
//-----------------------------------------------------------------------------------------------------------

function list_reseller_domains_($reseller_id,$domain=''){
	$local_connect = $GLOBALS['master_db'];
	if(!$domain){
		$query1 = "SELECT domain FROM domains WHERE reseller_id=$reseller_id";
		//$query2 = "SELECT d.domain, p.*, r.flags FROM purchases p LEFT JOIN registerdomain r ON(p.purchase_id=r.purchase_id) LEFT JOIN domains d ON(p.reseller_id=d.reseller_id AND d.domain=r.sld||'.'||r.tld) WHERE p.reseller_id=$reseller_id AND r.id IS NOT NULL AND d.domain IS NOT NULL ORDER BY p.expire_date DESC";
		//$query2 = "SELECT r.id, d.domain, p.*, r.flags, dnsdom.id as dns_dom_id, dns_dom_settings.dns_purchase_id FROM domains d LEFT JOIN purchases p ON(d.reseller_id=p.reseller_id) LEFT JOIN registerdomain r ON(p.purchase_id=r.purchase_id) LEFT JOIN dns_cluster.domains dnsdom ON(r.id=dnsdom.registerdomain_id) LEFT JOIN dns_cluster.domain_settings dns_dom_settings ON(dnsdom.id=dns_dom_settings.dns_dom_id) WHERE p.reseller_id=$reseller_id AND d.domain=r.sld||'.'||r.tld ORDER BY p.expire_date DESC";
		$query2 = "SELECT r.id, d.domain, p.*, r.flags, dnsdom.id as dns_dom_id, dns_dom_settings.dns_purchase_id FROM dns_cluster.domain_settings dns_dom_settings LEFT JOIN dns_cluster.domains dnsdom ON(dns_dom_settings.dns_dom_id=dnsdom.id) LEFT JOIN registerdomain r ON(dns_dom_settings.purchase_id=r.purchase_id) LEFT JOIN purchases p ON(dns_dom_settings.purchase_id=p.purchase_id) LEFT JOIN domains d ON(dns_dom_settings.reseller_id=d.reseller_id AND dns_dom_settings.store_id=d.store_id AND dns_dom_settings.dns_dom_name=d.domain) WHERE dns_dom_settings.reseller_id=$reseller_id AND r.id IS NOT NULL ORDER BY p.expire_date DESC";
	}else{
		$query1 = "SELECT domain FROM domains WHERE reseller_id=$reseller_id AND domain='$domain'";
		//$query2 = "SELECT d.domain, r.*, p.* FROM purchases p LEFT JOIN registerdomain r ON(p.purchase_id=r.purchase_id) LEFT JOIN domains d ON(p.reseller_id=d.reseller_id AND d.domain=r.sld||'.'||r.tld) WHERE p.reseller_id=$reseller_id AND r.id IS NOT NULL AND d.domain='$domain'";
		//$query2 = "SELECT r.id, d.domain, p.*, r.flags, dnsdom.id as dns_dom_id, dns_dom_settings.dns_purchase_id FROM domains d LEFT JOIN purchases p ON(d.reseller_id=p.reseller_id) LEFT JOIN registerdomain r ON(p.purchase_id=r.purchase_id) LEFT JOIN dns_cluster.domains dnsdom ON(r.id=dnsdom.registerdomain_id) LEFT JOIN dns_cluster.domain_settings dns_dom_settings ON(dnsdom.id=dns_dom_settings.dns_dom_id) WHERE p.reseller_id=$reseller_id AND d.domain=r.sld||'.'||r.tld AND d.domain='$domain' ORDER BY p.expire_date DESC";
		$query2 = "SELECT r.id, d.domain, p.*, r.flags, dnsdom.id as dns_dom_id, dns_dom_settings.dns_purchase_id FROM dns_cluster.domain_settings dns_dom_settings LEFT JOIN dns_cluster.domains dnsdom ON(dns_dom_settings.dns_dom_id=dnsdom.id) LEFT JOIN registerdomain r ON(dns_dom_settings.purchase_id=r.purchase_id) LEFT JOIN purchases p ON(dns_dom_settings.purchase_id=p.purchase_id) LEFT JOIN domains d ON(dns_dom_settings.reseller_id=d.reseller_id AND dns_dom_settings.store_id=d.store_id AND dns_dom_settings.dns_dom_name=d.domain) WHERE dns_dom_settings.reseller_id=$reseller_id AND r.id IS NOT NULL AND d.domain='$domain' ORDER BY p.expire_date DESC";
	}

	$local_connect->query($query2);
	if($local_db->error){
		mail('errors@bastun.net', 'list_reseller_domains_error', "QUERY : $query2");
		return false;
	}
	while($tmp = $local_connect->getNext()){
		$res[($tmp[domain])] = $tmp;
	}

	$local_connect->query($query1);
	if($local_db->error){
		mail('errors@bastun.net', 'list_reseller_domains_error', "QUERY : $query1");
		return false;
	}
	
	while($tmp = $local_connect->getNext()){
		if(!$res[($tmp[domain])]){
			$res[($tmp[domain])] = $tmp;
		}
	}
	
	return $res;
}
//-----------------------------------------------------------------------------------------------------------

function dns_check($domain){
	exec("dig $domain ns +short",$dns_result);
	$tmp = implode (' ',$GLOBALS['base_dns_settings']);
	$flag = false;
	foreach($dns_result as $k=>$v){
		if(preg_match("/".trim($v)."/i",$tmp) or preg_match("/$domain/i", $v)){
			$flag = true;
			break;
		}
	}
	return $flag;
}
//-----------------------------------------------------------------------------------------------------------

function include_($url){
	ob_start();

	readfile($url);
	$data .= ob_get_contents();

	ob_end_clean();

	preg_match_all("/{([\w\d]+)}/i",$data,$matches);

	foreach($matches[1] as $k=>$v){
		$data = preg_replace("/\{$v\}/",translate($v),$data);
	}
	echo $data;
}
//-----------------------------------------------------------------------------------------------------------

function search4domains($domain,$return_type=''){
	if(!preg_match("/^([a-z0-9][a-z0-9-]?)*[a-z0-9]\\.([a-z]{2,4})(\\.[a-z]{2,4})?$/", $domain) and !preg_match("/^([a-z0-9][a-z0-9-]*)?$/", $domain)){
		return false;
	}
	
	$tmp = explode('.', $domain);
	$local_connect = $GLOBALS['master_db'];
	$query = "SELECT check_domain('$tmp[0]') AS result";
	$local_connect->query($query);
	$raw_result = $local_connect->getNext();
	$raw_result = trim($raw_result['result']);
	$raw_result = explode("\n",$raw_result);
	foreach($raw_result as $k=>$v){
		if(trim($v)){
			$tmp = explode('|',$v);
			$res[(trim($tmp[0]))] = trim($tmp[1]);
		}
	}
	unset($tmp,$raw_result);

	switch ($return_type){
		case 'bTable' :
			$tbl = '<table cellspacing=0 cellpadding=0>';
			$map = array('notavailable'=>'Transfer','available'=>'Register');
			foreach($res as $k=>$v){
				$tbl .= "<tr><td class='$v'>$k</td><td class='$v'><a href=# onClick=\"choose_domain('$k');return false;\" onMouseover=\"tooltip_action('block','$v');\" onMouseout=\"tooltip_action('none','$v');\">".$map[$v]."</a></td><tr>";
			}
			$tbl .= '</table>';
			return $tbl;
		break;
		case 'myTable' :
			$tbl = '';
			$map = array('notavailable'=>'Transfer','available'=>'Register');
			foreach($res as $k=>$v){
				$tbl .= "<tr onClick=\"document.getElementById('dom_$k').click();\">
					<td id='td_$k' nowrap>
						<input type='radio' id='dom_$k' name='domain' value='".$v."_".$k."' onClick=\"bold_this('td_$k');show_domain_tooltip(this.value);\">".($v == 'notavailable' ? '<font color="#DA800E">'.$map[$v].'</font>' : '<font color="#006600">'.$map[$v].'</font>')." $k
					</td>
				</tr>";
			}
			return $tbl;
		break;
		default :
			return $res;
		break;	
	}
}
//-----------------------------------------------------------------------------------------------------------

function sort_periods($val1,$val2){
	$timestamp_val1 = strtotime($val1.'-01');
	$timestamp_val2 = strtotime($val2.'-01');
	if($timestamp_val1 == $timestamp_val2){
		return 0;
	}
	return ($timestamp_val1 > $timestamp_val2) ? -1 : 1;
}
//-----------------------------------------------------------------------------------------------------------

function getResellerInfo($reseller_id){
	$query = "SELECT first_name,last_name,company,email,address1,address2,city,state,zip,country,phone FROM resellers WHERE reseller_id=$reseller_id";
	$local_connect = $GLOBALS['master_db'];
	$local_connect->query($query);
	if($local_connect->error){
		mail('errors@bastun.net', 'error getting reseller details', $query);
		return false;
	}
	return $local_connect->getNext();
}
//-----------------------------------------------------------------------------------------------------------

function list_reseller_dns_domains($reseller_id){
	$local_connect = $GLOBALS['master_db'];
	$query = "SELECT d.reseller_id, d.registerdomain_id, ds.purchase_id, ds.dns_dom_id, ds.dns_dom_name, ds.mail_server, ds.open_store, ds.enabled, ds.checked, ds.dns_purchase_id, p.expire_date  FROM dns_cluster.domains d LEFT JOIN dns_cluster.domain_settings ds ON(d.id=ds.dns_dom_id) LEFT JOIN purchases p ON(ds.dns_purchase_id=p.purchase_id) WHERE d.reseller_id=$reseller_id AND ds.checked='t' AND ds.dns_purchase_id IS NOT NULL";
	$local_connect->query($query);
	if($local_connect->error){
		mail('errors@bastun.net', "list_reseller_dns_domains($reseller_id, $dom_id_list)", $query);
		echo $query;
		return false;
	}

	return $local_connect->getAll();
}
//-----------------------------------------------------------------------------------------------------------

function get_domain_records($dom_id, $filter=array()){
	$where = '';
	$local_connect = $GLOBALS['master_db'];
	if(!empty($filter)){
		foreach($filter as $k=>$v){
			$where .= " AND $k='$v'";
		}
	}
	$query = "SELECT r.id,r.domain_id,r.name,r.type,r.content,r.ttl,a.shared,a.used,a.type as addr_type FROM dns_cluster.records r LEFT JOIN dns_cluster.addresses a ON(r.content=a.address) WHERE r.domain_id=$dom_id $where";
	//return $query;
	$local_connect->query($query);
	if($local_connect->error){
		return $query;
		mail('errors@bastun.net', "get_domain_records($dom_id, $filter)", $query);
		return false;
	}

	return $local_connect->getAll();
}
//-----------------------------------------------------------------------------------------------------------

function getRpProductByPurchaseId($purchase_id){
	$local_connect = $GLOBALS['master_db'];
	$query = "SELECT rp.rp_product_id,rp.type,rp.name,p.expire_date,rp.services FROM purchases p LEFT JOIN rp_products rp USING(rp_product_id) WHERE p.purchase_id=$purchase_id";
	$local_connect->query($query);
	if($local_db->error){
		mail('errors@bastun.net', "getRpProductByPurchaseId($purchase_id)", $query);
		return false;
	}
	$res = $local_connect->getNext();
	return $res;
}
//-----------------------------------------------------------------------------------------------------------

function dns_dom_settings($dns_dom_id){
	$local_connect = $GLOBALS['master_db'];
	$query = "SELECT ds.dns_dom_name, ds.open_store, p.expire_date, ds.dns_purchase_id, ds.enabled, ds.mail_server, ds.users_count, p.expire_date - CURRENT_DATE AS expire_days FROM dns_cluster.domain_settings ds LEFT JOIN purchases p USING(purchase_id) WHERE dns_dom_id=$dns_dom_id";
        $local_connect->query($query);
        if($local_db->error){
                mail('errors@bastun.net', "dns_dom_settings($dns_dom_id)", $query);
                return false;
        }
        $res = $local_connect->getNext();
        return $res;	
}
//-----------------------------------------------------------------------------------------------------------

function managedDNSdomInfo($dns_dom_id){
	$dom_info = array();
	$dom_info['dns_dom_id'] = $dns_dom_id;
	$dom_info = array_merge($dom_info, dns_dom_settings($dom_info['dns_dom_id']));
	$dom_info = array_merge($dom_info, getRpProductByPurchaseId($dom_info['dns_purchase_id']));

	if($dom_info['shared_dns']){
		$dom_info['num_ips'] = 1;
	}elseif($dom_info['noshared_dns']){
		$dom_info['num_ips'] = 2;
	}

	if($dom_info['enabled'] == 't'){
		$dom_info['status'] = "<font color=green>Enabled</font>";
		$dom_info['dns_dom_action'] = "<input type='submit' value='Disable DNS' onClick=\"if(confirmSubmit('You are about to disable Manged DNS service for $dom_info[dns_dom_name]')){change_store_settings('take_action::disable|dns_dom_id::$dom_info[dns_dom_id]');}\">";
	}elseif($dom_info['enabled'] == 'f'){
		$dom_info['status'] = "<font color=red>Disabled</font>";
		$dom_info['dns_dom_action'] = "<input type='submit' value='Enable DNS' onClick=\"if(confirmSubmit('You are about to enable Manged DNS service for $dom_info[dns_dom_name]')){change_store_settings('take_action::enable|dns_dom_id::$dom_info[dns_dom_id]');}\">";
	}

        $records = get_domain_records($dom_info['dns_dom_id'], array('r.type'=>'NS'));
        foreach($records as $k=>$v){
                $ns_records[$k] = get_domain_records($dom_info['dns_dom_id'], array('r.name'=>$v['content']));
                $dnses[] = $v['content'];
                $ips[] = $ns_records[$k][0]['content'];
                $ip_types[] = $ns_records[$k][0]['shared'] == 't' ? 'Shared' : 'Dedicated';
        }

	$dom_info['dnses'] = implode('<br>', $dnses);
	$dom_info['ips'] = implode('<br>', $ips);
	$dom_info['ip_types'] = implode('<br>', $ip_types);

        if($dom_info['mail_server'] == 't'){
                $dom_info['mail_server_'] = 'mail.'.$dom_info['dns_dom_name'];
        }elseif($dom_info['mail_server'] == 'f'){
                $dom_info['mail_server_'] = "<img align='absmiddle' src='domain/images/ok_no_02.gif' alt='NO' width='11' height='17' border='0'> <a href='#' onClick=\"if(confirmSubmit('You are about to enable mail server mail.$dom_info[dns_dom_name]')){change_store_settings('take_action::set_mail_server|dns_dom_id::$dom_info[dns_dom_id]');}\">[set mail server]</a>";
        }
        
	return $dom_info;
}
//-----------------------------------------------------------------------------------------------------------

function chageStoreSettings($action, $params){
	$local_connect = $GLOBALS['master_db'];
	switch($action){
		case 'enable':
			$query = "BEGIN;UPDATE dns_cluster.domain_settings SET enabled='t',checked='f' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.domain_settings SET checked='t' WHERE dns_dom_id=$params[dns_dom_id];COMMIT;";
		break;
		case 'disable':
			$query = "BEGIN;UPDATE dns_cluster.domain_settings SET enabled='t',checked='f' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.domain_settings SET checked='t' WHERE dns_dom_id=$params[dns_dom_id];COMMIT;";			
		break;
		case 'set_mail_server':
			$query = "BEGIN;UPDATE dns_cluster.domain_settings SET mail_server='f',checked='f' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.domain_settings SET mail_server='t',checked='t' WHERE dns_dom_id=$params[dns_dom_id];COMMIT;";
		break;
		case 'change_a_record':
                        if($params['open_store'] == 'yes'){
                                $query ="BEGIN;UPDATE dns_cluster.domain_settings SET open_store='f',checked='f' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.domain_settings SET open_store='f', checked='t' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.records SET content='$params[store_a_record]' WHERE domain_id=$params[dns_dom_id] AND type='A' AND name IN((SELECT name FROM dns_cluster.domains WHERE id=$params[dns_dom_id]),'www.'||(SELECT name FROM dns_cluster.domains WHERE id=$params[dns_dom_id]));COMMIT;";
                        }else{
                                $query ="BEGIN;UPDATE dns_cluster.domain_settings SET open_store='t',checked='f' WHERE dns_dom_id=$params[dns_dom_id];UPDATE dns_cluster.domain_settings SET open_store='f', checked='t' WHERE dns_dom_id=$params[dns_dom_id];COMMIT;";
                        }
		break;

		$local_connect->query($query);
		if($local_connect->error){
			mail('errors@bastun.net', "chageStoreSettings($action, $params)", $query);
			return false;
		}
		return true;
	}
}

//-----------------------------------------------------------------------------------------------------------

function getDomainRecords($arr, $ret_type = ''){
	$local_connect = $GLOBALS['master_db'];
	foreach($arr as $k => $v){
		$where[] = "$k='$v'";	
	}

	if(!$where){
		return false;
	}

	$query = 'SELECT * FROM dns_cluster.records WHERE '.implode(' AND ', $where).' ORDER BY id';

	if($ret_type == 'query'){
		return $query;
	}

	$local_connect->query($query);
	if($local_connect->error){
		mail('errors@bastun.net', "Error: getDomainRecords($domain_id)", $query);
		return false;
	}

	$res = $local_connect->getAll();

	if(count($res) == 1){
		return $res[0];
	}

	return $res;
}
//-----------------------------------------------------------------------------------------------------------

function stripslashes_r($mix) {
	if (is_array($mix)) {
		foreach ($mix as $k => $v) {
			if (is_array($v)) {
				$mix[$k] = stripslashes_r($v);
			} else {
				$mix[$k] = stripslashes($v);
			}
		}
		return $mix;
	}
	return stripslashes($mix);
}
//-----------------------------------------------------------------------------------------------------------

function turn_off_magic_quotes_gpc(){
        if (get_magic_quotes_gpc()) {
                $_POST = stripslashes_r($_POST);
                $_GET = stripslashes_r($_GET);
                $_REQUEST = stripslashes_r($_REQUEST);
                $_COOKIE = stripslashes_r($_COOKIE);
        }
}
?>